package dao;

import entity.PersonBean;
import util.DBUtil;

import java.sql.*;

import java.util.ArrayList;
import java.util.List;

public class PersonDAO {
    /**
     * 删除一条数据
     *
     * @param
     * @return List<TaskBean>--符合条件的任务列表
     */
    public boolean deleteTask(int id) {
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        String sql = "delete from people where id=" + id;
        try {
            conn = DBUtil.getConnection();
            preparedStatement = conn.prepareStatement(sql);
            int res = preparedStatement.executeUpdate();
            if (res > 0) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            DBUtil.freeConnection(conn);
        }
        return false;
    }

    /**
     * 插入一条新数据
     *
     * @param
     * @return List<TaskBean>--符合条件的任务列表
     */
    public boolean addTask(PersonBean personBean) {
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        String sql = "";
        sql = "insert into people(worknumber, name, department, worktime, degree, profession, title, phonenumber, address, introduction) values(?,?,?,?,?,?,?,?,?,?)";

        try {
            conn = DBUtil.getConnection();
            preparedStatement = conn.prepareStatement(sql);

            preparedStatement.setString(1, personBean.getWorknumber());
            preparedStatement.setString(2, personBean.getName());
            preparedStatement.setString(3, personBean.getDepartment());
            preparedStatement.setString(4, personBean.getWorktime());
            preparedStatement.setString(5, personBean.getDegree());
            preparedStatement.setString(6, personBean.getProfession());
            preparedStatement.setString(7, personBean.getTitle());
            preparedStatement.setString(8, personBean.getPhonenumber());
            preparedStatement.setString(9, personBean.getAddress());
            preparedStatement.setString(10, personBean.getIntroduction());
            int rowsUpdated = preparedStatement.executeUpdate();
            if (rowsUpdated > 0) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            DBUtil.freeConnection(conn);
        }
        return false;
    }

    /**
     * 修改id匹配的一行数据
     *
     * @param personBean
     * @return List<TaskBean>--符合条件的任务列表
     */
    public boolean updateTask(PersonBean personBean) {
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        String sql = "";
        if (personBean.getId() != 0) {
            sql = "update people set worknumber=?, name=?, department=?, worktime=?, degree=?, profession=?, title=?, phonenumber=?, address=?, introduction=? where id=" + personBean.getId();
        }
        try {
            conn = DBUtil.getConnection();
            preparedStatement = conn.prepareStatement(sql);

            preparedStatement.setString(1, personBean.getWorknumber());
            preparedStatement.setString(2, personBean.getName());
            preparedStatement.setString(3, personBean.getDepartment());
            preparedStatement.setString(4, personBean.getWorktime());
            preparedStatement.setString(5, personBean.getDegree());
            preparedStatement.setString(6, personBean.getProfession());
            preparedStatement.setString(7, personBean.getTitle());
            preparedStatement.setString(8, personBean.getPhonenumber());
            preparedStatement.setString(9, personBean.getAddress());
            preparedStatement.setString(10, personBean.getIntroduction());
            int rowsUpdated = preparedStatement.executeUpdate();
            if (rowsUpdated > 0) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            DBUtil.freeConnection(conn);
        }
        return false;
    }

    /**
     * 查询id匹配的一行数据
     *
     * @param id 主键
     * @return List<TaskBean>--符合条件的任务列表
     */
    public PersonBean findTaskBean(int id) {
        PersonBean obj = new PersonBean();
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        String sql = "select * from people where id=" + id;
        try {
            conn = DBUtil.getConnection();
            preparedStatement = conn.prepareStatement(sql);
            rs = preparedStatement.executeQuery();
            rs.next();

            obj.setId(rs.getInt("id"));
            obj.setWorknumber(rs.getString("worknumber"));
            obj.setName(rs.getString("name"));
            obj.setPhonenumber(rs.getString("phonenumber"));
            obj.setDepartment(rs.getString("department"));
            obj.setWorktime(rs.getString("worktime"));
            obj.setDegree(rs.getString("degree"));
            obj.setProfession(rs.getString("profession"));
            obj.setTitle(rs.getString("title"));
            obj.setAddress(rs.getString("address"));
            obj.setIntroduction(rs.getString("introduction"));
            return obj;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            DBUtil.freeConnection(conn);
        }
        return obj;
    }


    /**
     * 查询满足条件的任务列表
     *
     * @param name 姓名,worknumber 工号,department 部门,worktime 入职时间,profession 专业,curpage 当前页码
     * @return List<TaskBean>--符合条件的任务列表
     */
    public List<PersonBean> findTask(String name, String worknumber, ArrayList<String> department, String worktime, String profession, int curpage) {
        List<PersonBean> taskList = new ArrayList<PersonBean>();
        String selectSql = "select * from people where 1=1 ";
        String sql = "";
        if (name != "") {
            sql += "and name like '%" + name + "%' ";
        }
        if (worknumber != "") {
            sql += "and worknumber like '%" + worknumber + "%' ";
        }
        if (!department.isEmpty()) {
            if(department.size()==1&&department.get(0).equals("")){
            }else {
                sql += " and department in " + "(";
                for (int i = 0; i < department.size(); i++) {
                    sql += "'" + department.get(i) + "'";
                    if (i != department.size() - 1)
                        sql += ",";
                }
                sql += ")";
            }
        }
        if (worktime != "") {
            sql += "and worktime='" + worktime + "' ";
        }
        if (profession != "") {
            sql += "and profession like '%" + profession + "%' ";
        }
        selectSql += sql;

        int page_size = 6;
        int start = curpage * page_size - page_size;
        String pageSql = " LIMIT " + start + ", " + page_size;
        selectSql += pageSql;

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(selectSql);
            // 访问数据
            while (rs.next()) {
                PersonBean obj = new PersonBean();
                obj.setId(rs.getInt("id"));
                obj.setWorknumber(rs.getString("worknumber"));
                obj.setName(rs.getString("name"));
                obj.setPhonenumber(rs.getString("phonenumber"));
                obj.setDepartment(rs.getString("department"));
                obj.setWorktime(rs.getString("worktime"));
                obj.setDegree(rs.getString("degree"));
                obj.setProfession(rs.getString("profession"));
                obj.setTitle(rs.getString("title"));
                obj.setAddress(rs.getString("address"));
                obj.setIntroduction(rs.getString("introduction"));
                taskList.add(obj);
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            try {
                stmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            DBUtil.freeConnection(conn);
        }
        return taskList;
    }

    /**
     * 获取总页数
     *
     * @param name 姓名,worknumber 工号,department 部门,worktime 入职时间,profession 专业,curpage 当前页码
     * @return List<TaskBean>--符合条件的任务列表
     */
    public int findPageCount(String name, String worknumber, ArrayList<String> department, String worktime, String profession) {
        String countSql = "select count(*) from people where 1=1 ";
        String sql = "";
        if (name != "") {
            sql += "and name like '%" + name + "%' ";
        }
        if (worknumber != "") {
            sql += "and worknumber like '%" + worknumber + "%' ";
        }
        if (!department.isEmpty()) {
            if(department.size()==1&&department.get(0).equals("")){
            }else {
                sql += " and department in " + "(";
                for (int i = 0; i < department.size(); i++) {
                    sql += "'" + department.get(i) + "'";
                    if (i != department.size() - 1)
                        sql += ",";
                }
                sql += ")";
            }
        }

        if (worktime != "") {
            sql += "and worktime='" + worktime + "' ";
        }
        if (profession != "") {
            sql += "and profession like '%" + profession + "%' ";
        }
        countSql += sql;

        int totalCount = 0;
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtil.getConnection();
            pstmt = connection.prepareStatement(countSql);
            resultSet = pstmt.executeQuery();
            if (resultSet.next()) {
                totalCount = resultSet.getInt(1);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if(resultSet!=null)
                    resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            try {
                if(pstmt!=null)
                    pstmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            if(connection!=null)
                DBUtil.freeConnection(connection);
        }
        int page_size = 6;
        return ((totalCount + page_size) - 1) / page_size;
    }
}
